package com.java.st.lib.tools.io.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.java.st.lib.tools.collection.ListUtil;
import com.java.st.lib.tools.collection.MapUtil;
import com.java.st.lib.tools.io.URLResourceUtil;
import com.java.st.lib.tools.number.NumberUtil;

public class ExcelUtil {
	private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private static final String EMPTY_STRING = "";

	public static <T> void export(List<T> models, OutputStream stream) throws IOException {
		if (ListUtil.isEmpty(models)) {
			return;
		}
		SheetInfo sheetInfo = resolveSheetInfo(models.get(0).getClass());
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();
		XSSFRow headerRow = sheet.createRow(0);
		List<String> columnNames = sheetInfo.getColumnNames();
		for (int i = 0; i < columnNames.size(); i++) {
			XSSFCell cell = headerRow.createCell(i);
			cell.setCellValue(columnNames.get(i));
		}

		List<Field> columnFields = sheetInfo.getColumnFields();
		for (int i = 0; i < models.size(); i++) {
			XSSFRow dataRow = sheet.createRow(i + 1);
			for (int j = 0; j < columnFields.size(); j++) {
				XSSFCell cell = dataRow.createCell(j);
				String fieldVal = getFieldValueStr(models.get(i), columnFields.get(j));
				cell.setCellValue(fieldVal);
			}
		}
		workbook.write(stream);
		workbook.close();
	}

	public static <T> void export(Map<String, List<T>> modelMap, OutputStream stream) throws IOException {
		if (MapUtil.isEmpty(modelMap)) {
			return;
		}
		XSSFWorkbook workbook = new XSSFWorkbook();
		for (Entry<String, List<T>> entry : modelMap.entrySet()) {
			String sheetName = entry.getKey();
			List<T> models = entry.getValue();
			SheetInfo sheetInfo = resolveSheetInfo(models.get(0).getClass());
			XSSFSheet sheet = workbook.createSheet(sheetName);
			XSSFRow headerRow = sheet.createRow(0);
			List<String> columnNames = sheetInfo.getColumnNames();
			for (int i = 0; i < columnNames.size(); i++) {
				XSSFCell cell = headerRow.createCell(i);
				cell.setCellValue(columnNames.get(i));
			}

			List<Field> columnFields = sheetInfo.getColumnFields();
			for (int i = 0; i < models.size(); i++) {
				XSSFRow dataRow = sheet.createRow(i + 1);
				for (int j = 0; j < columnFields.size(); j++) {
					XSSFCell cell = dataRow.createCell(j);
					String fieldVal = getFieldValueStr(models.get(i), columnFields.get(j));
					cell.setCellValue(fieldVal);
				}
			}
		}
		workbook.write(stream);
		workbook.close();
	}

	public static void export(Map<String, List<?>> modelMap, String[] titles, OutputStream stream) throws IOException {
		if (MapUtil.isEmpty(modelMap)) {
			return;
		}
		XSSFWorkbook workbook = new XSSFWorkbook();
		int titleIndex = 0;
		for (Entry<String, List<?>> entry : modelMap.entrySet()) {
			String sheetName = entry.getKey();
			List<?> models = entry.getValue();
			SheetInfo sheetInfo = resolveSheetInfo(models.get(0).getClass());
			XSSFSheet sheet = workbook.createSheet(sheetName);
			XSSFRow headerRow = sheet.createRow(0);
			List<String> columnNames = sheetInfo.getColumnNames();
			if (StringUtils.isNotEmpty(titles[titleIndex])) {
				XSSFRow titleRow = sheet.createRow(0);
				XSSFCell cell = titleRow.createCell(0);
				cell.setCellValue(titles[titleIndex]);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNames.size() - 1));
				headerRow = sheet.createRow(1);
			}
			for (int i = 0; i < columnNames.size(); i++) {
				XSSFCell cell = headerRow.createCell(i);
				cell.setCellValue(columnNames.get(i));
			}
			List<Field> columnFields = sheetInfo.getColumnFields();
			int dataBeginRow = headerRow.getRowNum() + 1;
			for (int i = 0; i < models.size(); i++) {
				XSSFRow dataRow = sheet.createRow(dataBeginRow++);
				for (int j = 0; j < columnFields.size(); j++) {
					XSSFCell cell = dataRow.createCell(j);
					String fieldVal = getFieldValueStr(models.get(i), columnFields.get(j));
					cell.setCellValue(fieldVal);
				}
			}
			titleIndex++;
		}
		workbook.write(stream);
		workbook.close();
	}

	public static void export(String templatePath, Map<Integer, List<?>> modelMap, String[] titles, int[] dataBeginRows,
			short rowHeight, OutputStream stream) throws IOException {
		if (MapUtil.isEmpty(modelMap)) {
			return;
		}
		XSSFWorkbook workbook = new XSSFWorkbook(URLResourceUtil.asStream(templatePath));
		XSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		CreationHelper createHelper = workbook.getCreationHelper();
		XSSFFont linkFont = workbook.createFont();
		XSSFCellStyle linkStyle = workbook.createCellStyle();
		linkStyle.setBorderBottom(BorderStyle.THIN);
		linkStyle.setBorderLeft(BorderStyle.THIN);
		linkStyle.setBorderRight(BorderStyle.THIN);
		linkStyle.setBorderTop(BorderStyle.THIN);
		for (Entry<Integer, List<?>> entry : modelMap.entrySet()) {
			Integer sheetIndex = entry.getKey();
			List<?> models = entry.getValue();
			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
			if (StringUtils.isNotEmpty(titles[sheetIndex])) {
				XSSFRow titleRow = sheet.getRow(0);
				XSSFCell cell = titleRow.getCell(0);
				XSSFCellStyle cellStyleWrap = cell.getCellStyle();
				cellStyleWrap.setWrapText(true);
				cell.setCellValue(titles[sheetIndex]);
			}
			if (ListUtil.isNotEmpty(models)) {
				SheetInfo sheetInfo = resolveSheetInfo(models.get(0).getClass());
				List<Field> columnFields = sheetInfo.getColumnFields();
				for (int i = 0; i < models.size(); i++) {
					XSSFRow dataRow = sheet.createRow(dataBeginRows[sheetIndex]++);
					dataRow.setHeight(rowHeight);
					for (int j = 0; j < columnFields.size(); j++) {
						XSSFCell cell = dataRow.createCell(j);
						String fieldVal = getFieldValueStr(models.get(i), columnFields.get(j));
						if (StringUtils.startsWith(fieldVal, "http")) {
							XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
							link.setAddress(fieldVal);
							cell.setHyperlink(link);
							linkFont.setUnderline((byte) 1);
							linkFont.setColor(IndexedColors.BLUE.getIndex());
							linkStyle.setFont(linkFont);
							cell.setCellStyle(linkStyle);
						} else if (NumberUtil.isNumber(fieldVal)) {
							cell.setCellType(CellType.NUMERIC);
							cell.setCellStyle(cellStyle);
						} else {
							cell.setCellType(CellType.STRING);
							cell.setCellStyle(cellStyle);
						}
						cell.setCellValue(fieldVal);
					}
				}
			}
		}
		workbook.write(stream);
		workbook.close();
	}

	public static <T> List<T> parse(InputStream inputStream, Class<T> modelClass, int startRowIndex,
			int startColumnIndex) throws IOException {
		List<T> modelList = new ArrayList<T>();

		if (inputStream == null || modelClass == null) {
			return modelList;
		}

		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(inputStream);
		} catch (InvalidFormatException e) {
			return modelList;
		}
		Sheet sheet = workbook.getSheetAt(0);
		SheetInfo sheetInfo = resolveSheetInfo(modelClass);
		List<Field> columnFields = sheetInfo.getColumnFields();
		for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				break;
			}
			T model;
			try {
				model = modelClass.newInstance();
			} catch (InstantiationException e) {
				return modelList;
			} catch (IllegalAccessException e) {
				return modelList;
			}

			boolean isEmptyRow = true;
			for (int j = 0; j < sheetInfo.getColumnFields().size(); j++) {
				Cell cell = row.getCell(startColumnIndex + j);
				Object cellVal = getCellValue(cell, columnFields.get(j));
				if (cellVal != null) {
					isEmptyRow = false;
				}
				setFieldValue(model, cellVal, columnFields.get(j));
			}

			if (isEmptyRow) {
				break;
			}
			modelList.add(model);
		}

		return modelList;
	}

	public static <T> List<T> parse(InputStream inputStream, int sheetIndex, Class<T> modelClass, int startRowIndex,
			int startColumnIndex) throws IOException {
		List<T> modelList = new ArrayList<T>();

		if (inputStream == null || modelClass == null) {
			return modelList;
		}

		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(inputStream);
		} catch (InvalidFormatException e) {
			return modelList;
		}
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		SheetInfo sheetInfo = resolveSheetInfo(modelClass);
		List<Field> columnFields = sheetInfo.getColumnFields();
		for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				break;
			}

			T model;
			try {
				model = modelClass.newInstance();
			} catch (InstantiationException e) {
				return modelList;
			} catch (IllegalAccessException e) {
				return modelList;
			}

			boolean isEmptyRow = true;
			for (int j = 0; j < sheetInfo.getColumnFields().size(); j++) {
				Cell cell = row.getCell(startColumnIndex + j);
				Object cellVal = getCellValue(cell, columnFields.get(j));
				if (cellVal != null) {
					isEmptyRow = false;
				}
				setFieldValue(model, cellVal, columnFields.get(j));
			}

			if (isEmptyRow) {
				break;
			}
			modelList.add(model);
		}

		return modelList;
	}

	public static <T> List<T> parse(File excelFile, Class<T> modelClass, int startRowIndex, int startColumnIndex)
			throws FileNotFoundException, IOException {
		if (excelFile == null || modelClass == null) {
			return new ArrayList<T>();
		}

		return parse(new FileInputStream(excelFile), modelClass, startRowIndex, startColumnIndex);
	}

	private static Object getCellValue(Cell cell, Field field) {
		if (cell == null) {
			return null;
		}

		try {
			if (field.getType() == Double.class || field.getType() == double.class) {
				return Double.parseDouble(getCellValueStr(cell));
			}

			if (field.getType() == Float.class || field.getType() == float.class) {
				return Float.parseFloat(getCellValueStr(cell));
			}

			if (field.getType() == Long.class || field.getType() == long.class) {
				return Long.parseLong(getCellValueStr(cell));
			}

			if (field.getType() == Integer.class || field.getType() == int.class) {
				return Integer.parseInt(getCellValueStr(cell));
			}

			if (field.getType() == Date.class) {
				return dateFormat.parse(getCellValueStr(cell));
			}
			cell.setCellType(CellType.STRING);
			return cell.getStringCellValue();
		} catch (Exception e) {
			return null;
		}

	}

	private static <T> String getFieldValueStr(T model, Field field) {
		Object fieldVal = null;

		field.setAccessible(true);
		try {
			fieldVal = field.get(model);
		} catch (IllegalArgumentException e) {
		} catch (IllegalAccessException e) {
		}

		if (fieldVal == null) {
			return EMPTY_STRING;
		}

		if (field.getType() == Date.class) {
			return dateFormat.format((Date) fieldVal);
		}

		return String.valueOf(fieldVal);
	}

	private static String getCellValueStr(Cell cell) {
		if (cell == null) {
			return null;
		}

		try {
			if (cell.getCellType() == CellType.NUMERIC) {
				return String.valueOf(cell.getNumericCellValue());
			} else if (cell.getCellType() == CellType.BOOLEAN) {
				return String.valueOf(cell.getBooleanCellValue());
			} else {
				return cell.getStringCellValue();
			}
		} catch (Exception e) {
			return null;
		}
	}

	private static <T> void setFieldValue(T model, Object val, Field field) {
		if (val == null || field == null || field.getType() != val.getClass()) {
			return;
		}
		field.setAccessible(true);
		try {
			field.set(model, val);
		} catch (IllegalArgumentException e) {
		} catch (IllegalAccessException e) {
		}
	}

	private static <T> SheetInfo resolveSheetInfo(Class<T> modelClass) {
		Field[] fields = modelClass.getDeclaredFields();
		Arrays.sort(fields, new Comparator<Field>() {
			@Override
			public int compare(Field arg0, Field arg1) {
				ExcelConfig config0 = arg0.getAnnotation(ExcelConfig.class);
				ExcelConfig config1 = arg1.getAnnotation(ExcelConfig.class);
				if (config0 == null && config1 == null) {
					return 0;
				}
				if (config0 == null) {
					return -1;
				}
				if (config1 == null) {
					return 1;
				}
				if (config0.index() > config1.index()) {
					return 1;
				} else if (config0.index() < config1.index()) {
					return -1;
				}
				return 0;
			}
		});
		List<String> columnNames = new ArrayList<String>();
		List<Field> columnFields = new ArrayList<Field>();
		for (Field field : fields) {
			if (Modifier.isStatic(field.getModifiers())) {
				continue;
			}
			ExcelConfig conf = field.getDeclaredAnnotation(ExcelConfig.class);
			if (conf != null && conf.igonre()) {
				continue;
			}
			if (conf == null || StringUtils.isEmpty(conf.columnName())) {
				columnNames.add(field.getName());
			} else {
				columnNames.add(conf.columnName());
			}
			columnFields.add(field);
		}
		SheetInfo sheetInfo = new SheetInfo();
		sheetInfo.setColumnFields(columnFields);
		sheetInfo.setColumnNames(columnNames);
		return sheetInfo;
	}
}
